﻿USE master
GO
DROP DATABASE IgmDb
GO
CREATE DATABASE IgmDb --InternGraduationManagementDb
GO
USE IgmDb
GO
CREATE TABLE [User] 
(
	Id int primary key identity(1,1),
	Permission int not null, -- 0 = Admin, 1 = TrK, 2 = TLDT
	Name nvarchar(50) unique not null, 
	[Password] nvarchar(80)not null
)
GO
CREATE TABLE Major
(
	Id int primary key identity(1,1),
	Code nvarchar(4) unique not null, -- THUD / CNPM
	Name nvarchar(50) unique not null -- Tin hoc ung dung
)
GO
CREATE TABLE KindIntern    
(
	Id int primary key identity(1,1),
	[Year] int not null,
	Name nvarchar(50) unique not null
)
GO
CREATE TABLE Student
(
	Id int primary key identity(1,1),
	Code nvarchar(8) unique not null, -- CQ080585
	Name nvarchar(50) not null, -- Le Thi Thanh Hai
	IdMajor int references Major(Id) not null,
	DateOfBirth datetime,
	Term bit not null, -- 0 : SV nam 3, 1: SV nam 4
	Thesis nvarchar(MAX)
)
GO
CREATE TABLE Lecturer 
(
	Id int primary key identity(1,1),
	IdMajor int references Major(Id) not null,
	Name nvarchar(50) not null,-- Ninh Thi Thanh Tam
	DateOfBirth datetime,
	UniversityDegree nvarchar(50) not null, -- Hoc vi; Cu nhan, Thac si, Tien si
)
GO
CREATE TABLE KindMark    
(
	Id int primary key identity(1,1),
	Name nvarchar(50) not null
)
GO
CREATE TABLE StudentMark    
(
	Id int primary key identity(1,1),
	IdKindMark int references KindMark(Id),
	IdStudent int references Student(Id),
	Mark float,
	IsDebt bit -- Con no mon khong 1 = Co
)
GO
CREATE TABLE [Group]   
(
	Id int primary key identity(1,1),
	IdKindIntern int references KindIntern(Id) not null,
	Name nvarchar(50) unique not null, -- Nhom1
	NumberMembers int not null,
	Project nvarchar(MAX)
)
GO
CREATE TABLE GroupStudent
(
	Id int primary key identity(1,1),
	IdGroup int references [Group](Id) not null,
	IdStudent int references Student(Id) not null,
	IsLeader bit not null -- 1: la nhom truong
)
GO
CREATE TABLE GroupLeturer
(
	Id int primary key identity(1,1),
	IdGroups int references [Group](Id) not null,
	IdLecturer int references Lecturer(Id) not null,
	[Year] int not null
)
GO
CREATE TABLE StudentLeturer
(
	Id int primary key identity(1,1),
	IdStudent int references Student(Id) not null,
	IdLecturer int references Lecturer(Id) not null,
	[Year] int not null
)
GO
CREATE TABLE Subcommittee
(
	Id int primary key identity(1,1),
	IdKindIntern int references KindIntern(Id) not null,
	Name nvarchar(50) unique not null, -- Nhom1
	NumberMembers int not null
)
CREATE TABLE SubcommitteeLecture
(
	Id int primary key identity(1,1),
	IdSubcommittee int references Subcommittee(Id) not null,
	IdLecturer int references Lecturer(Id) not null,
)
CREATE TABLE SubcommitteeGroup
(
	Id int primary key identity(1,1),
	IdSubcommittee int references Subcommittee(Id) not null,
	IdGroup int references [Group](Id) not null,
)
CREATE TABLE LastModifyData -- It change for all userId in every where
(
	Id int primary key identity(1,1),
	IdUser int references [User](Id) not null,
	LastTime DateTime not null,
	ChangeLog nvarchar(250) not null,
)

-----------------------------------------------------------------------------------------------------
-------------------------------------------- INSERT DATA --------------------------------------------
-----------------------------------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[User] ON
GO
INSERT INTO [dbo].[User]([Id],[Permission],[Name], [Password])VALUES(1,0,'Admin','123456')
INSERT INTO [dbo].[User]([Id],[Permission],[Name], [Password])VALUES(2,1,'Truong khoa','123456')
INSERT INTO [dbo].[User]([Id],[Permission],[Name], [Password])VALUES(3,2,'Tro ly dao tao','123456')
GO
SET IDENTITY_INSERT [dbo].[User] OFF
GO
-----------------------------------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[Major] ON
GO
INSERT INTO [dbo].[Major]([Id],[Code],[Name])VALUES(1,'CNTT',N'Công nghệ thông tin')
INSERT INTO [dbo].[Major]([Id],[Code],[Name])VALUES(2,'QLGD',N'Quản lý giáo dục')
INSERT INTO [dbo].[Major]([Id],[Code],[Name])VALUES(3,'TLGD',N'Tâm lý giáo dục')
GO
SET IDENTITY_INSERT [dbo].[Major] OFF
GO
-----------------------------------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[KindIntern] ON
GO
INSERT INTO [dbo].[KindIntern]([Id],[Year],[Name])VALUES(1,2012,'Thực tập cơ sở')
INSERT INTO [dbo].[KindIntern]([Id],[Year],[Name])VALUES(2,2012,'Thực tập tốt nghiệp')
INSERT INTO [dbo].[KindIntern]([Id],[Year],[Name])VALUES(3,2012,'Khóa luận tốt nghiệp')
GO
SET IDENTITY_INSERT [dbo].[KindIntern] OFF
GO
-----------------------------------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[Student] ON
GO
INSERT INTO [dbo].[Student]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt],[Term],[Thesis])VALUES(1,'CQ080501',N'Nguyễn Thị A',1,null,1,N'Đề tài của Nguyễn Thị A')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(2,'CQ080502',N'Trần Văn B',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(3,'CQ080503',N'Lê Văn C',1,null,1,N'Đề tài của Lê Văn C')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(4,'CQ080504',N'Hoàng Thị D',1,null,1,N'Đề tài của Hoàng Thị D')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(5,'CQ080505',N'Phạm Văn E',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(6,'CQ080506',N'Phan Thị F',1,null,1,N'Đề tài của Phạm Văn E')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(7,'CQ080507',N'Vũ Văn G',1,null,1,N'Đề tài của Vũ Văn G')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(8,'CQ080508',N'Đặng Thị H',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(9,'CQ080509',N'Bùi Thị I',1,null,1,N'Đề tài của Bùi Thị I')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(10,'CQ080510',N'Đỗ Văn K',1,null,1,N'Đề tài của Đỗ Văn K')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(11,'CQ080511',N'Hồ Thị L',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(12,'CQ080512',N'Ngô Văn M',1,null,1,N'Đề tài của Ngô Văn M')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(13,'CQ080513',N'Dương Thị N',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(14,'CQ080514',N'Đào Văn O',1,null,1,N'Đề tài của Đào Văn O')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(15,'CQ080515',N'Đoàn Thị P',1,null,1,N'Đề tài của Đoàn Thị P')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(16,'CQ080516',N'Lý Văn Q',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(17,'CQ080517',N'Vương Thị U',1,null,1,N'Đề tài của Vương Thị U')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(18,'CQ080518',N'Trịnh Văn X',1,null,1,N'Đề tài của Trịnh Văn X')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(19,'CQ080519',N'Trương Thị Y',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(20,'CQ080520',N'Đinh Văn T',1,null,1,N'Đề tài của Đinh Văn T')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(21,'CQ080521',N'Lâm Thị  A',1,null,1,N'Đề tài của Lâm Thị  A')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(22,'CQ080522',N'Phùng Văn B',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(23,'CQ080523',N'Mai Thị C',1,null,1,N'Đề tài của Mai Thị C')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(24,'CQ080524',N'Tô Văn D',1,null,1,N'Đề tài của Tô Văn D')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(25,'CQ080525',N'Trương Thị E',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(26,'CQ080526',N'Hà Văn F',1,null,1,N'Đề tài của Hà Văn F')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(27,'CQ080527',N'Triệu Thị G',1,null,1,N'Đề tài của Triệu Thị G')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(28,'CQ080528',N'Lưu Văn H',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(29,'CQ080529',N'Quách Thị I',1,null,1,N'Đề tài của Quách Thị I')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(30,'CQ080530',N'Cao Thị K',1,null,1,N'Đề tài của Cao Thị K')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(31,'CQ080531',N'Chu Văn L',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(32,'CQ080532',N'Vương Thị M',1,null,1,N'Đề tài của Vương Thị M')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(33,'CQ080533',N'Châu Văn N',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(34,'CQ080534',N'Từ Văn O',1,null,1,N'Đề tài của Từ Văn O')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(35,'CQ080535',N'Lê Thị P',1,null,1,N'Đề tài của Lê Thị P')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(36,'CQ080536',N'Nguyễn Văn Q',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(37,'CQ080537',N'Trần Văn R',1,null,1,N'Đề tài của Trần Văn R')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(38,'CQ080538',N'Đỗ Thị S',1,null,1,N'Đề tài của Đỗ Thị S')
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(39,'CQ080539',N'Phạm Thị Y',1,null,0,null)
INSERT INTO [dbo].[Student]([Id],[Code],[Name],[IdMajor],[DateOfBirth],[Term],[Thesis])VALUES(40,'CQ080540',N'Dương Văn T',1,null,1,N'Đề tài của Dương Văn T')
GO
SET IDENTITY_INSERT [dbo].[Student] OFF
GO
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[Lecturer] ON
GO
INSERT INTO [dbo].[Lecturer]([Id],[IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES(1,'1',N'Ninh Thị T',null,N'Thạc sĩ')
INSERT INTO [dbo].[Lecturer]([Id],[IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES(2,'2',N'Trần Thị N',null,N'Cử nhân')
INSERT INTO [dbo].[Lecturer]([Id],[IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES(3,'1',N'Phạm Quang T',null,N'Tiến sĩ')
INSERT INTO [dbo].[Lecturer]([Id],[IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES(4,'1',N'Hoàng Phan A',null,N'Cử nhân')
INSERT INTO [dbo].[Lecturer]([Id],[IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES(5,'2',N'Nguyễn Thị N',null,N'Thạc sĩ')
INSERT INTO [dbo].[Lecturer]([Id],[IdMajor],[Name],[DateOfBirth],[UniversityDegree ])VALUES(6,'1',N'Lê Quang T',null,N'Thạc sĩ')
GO
SET IDENTITY_INSERT [dbo].[Lecturer] OFF
GO
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[KindMark] ON
GO
INSERT INTO [dbo].[KindMark]([Id],[Name])VALUES(1,'K5')
INSERT INTO [dbo].[KindMark]([Id],[Name])VALUES(2,'N3')
INSERT INTO [dbo].[KindMark]([Id],[Name])VALUES(3,'TT')
INSERT INTO [dbo].[KindMark]([Id],[Name])VALUES(4,'TN')
GO
SET IDENTITY_INSERT [dbo].[KindMark] OFF
GO
-----------------------------------------------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[StudentMark] ON
GO
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(1,1,1,6.8,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(2,1,2,7.7,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(3,1,3,5.4,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(4,1,4,6.5,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(5,1,5,7.5,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(6,1,6,6.6,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(7,1,7,7.5,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(8,1,8,5.1,true)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(9,1,9,6.4,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(10,1,10,7.4,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(11,1,11,6.3,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(12,1,12,7.1,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(13,1,13,5.4,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(14,1,14,6.9,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(15,1,15,7.9,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(16,1,16,6.8,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(17,1,17,7.0,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(18,1,18,5.6,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(19,1,19,6.4,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(20,1,20,7.4,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(21,1,21,6.1,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(22,1,22,6.7,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(23,1,23,5.6,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(24,1,24,6.4,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(25,1,25,7.4,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(26,1,26,6.9,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(27,1,27,7.6,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(28,1,28,5.8,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(29,1,29,6.3,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(30,1,30,7.1,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(31,1,31,6.6,true)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(32,1,32,3.7,true)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(33,1,33,5.4,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(34,1,34,6.7,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(35,1,35,7.1,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(36,1,36,6.7,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(37,1,37,7.9,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(38,1,38,5.2,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(39,1,39,6.3,false)
INSERT INTO [dbo].[StudentMark]([Id],[IdKindMark],[IdStudent],[Mark],[IsDebt])VALUES(40,1,40,7.8,false)
GO
SET IDENTITY_INSERT [dbo].[Student] OFF
GO